package lib.data;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

import lib.base.EgoException;
import lib.base.EgoHolidayManager;
import lib.base.EgoSystem;

/**
 * Customer class
 * 
 * @author jonas
 *
 */
public class Customer extends Data
{
	private ArrayList<Department> allDepartments;
	private ArrayList<Employee> allEmployees;
	public EgoHolidayManager holidayManager;
	
	/**
	 * Construct Customer Class
	 * 
	 * @param id the customer's ID
	 */
	public Customer(int id) throws Exception
	{
		super("customer");
		addPrimaryKey("customerID", Integer.toString(id));
		
		refresh();
	}

	/**
	 * (re)loads the employees,departments and relations
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public void refresh() throws Exception
	{
		loadData();
		
		// open the employee's calendar service
		String user, pwd;
		if (! field.get("temporarySystemAccount").toString().isEmpty())
		{
			HashMap<String, String> accounts = (HashMap<String, String>) EgoSystem.config.get("temporarySystemAccounts");
			user = field.get("temporarySystemAccount").toString();
			pwd = accounts.get(user);
		} else
		{
			user = field.get("systemAccount").toString();
			pwd = field.get("password").toString();
		}
		holidayManager = new EgoHolidayManager(user, pwd);
		
		_loadEmployees();
		_loadDepartments();
		_loadDepartmentEmployeeRelations();
	}
	
	private void _loadDepartmentEmployeeRelations() throws Exception
	{
		for (int i=0; i<allDepartments.size(); i++)
		{
			allDepartments.get(i).loadEmployees();
		}
		for (int i=0; i<allEmployees.size(); i++)
		{
			allEmployees.get(i).loadDepartments();
		}
	}
	
	/**
	 * load employees of this customer
	 * 
	 */
	private void _loadEmployees() throws Exception
	{
		allEmployees = new ArrayList<Employee>();

		// sql query
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
			"SELECT employeeID FROM `employee` WHERE customerID="+field.get("customerID")
		);
		while (rs.next())
		{
			Employee e = new Employee(rs.getInt("employeeID"), this);
			allEmployees.add(e);
		}
		rs.close();
		stm.close();
	}

	/**
	 * load departments of this customer
	 * 
	 */
	private void _loadDepartments() throws Exception
	{
		allDepartments = new ArrayList<Department>();

		// sql query
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
			"SELECT departmentID FROM `department` WHERE customerID="+field.get("customerID")
		);
		while (rs.next())
		{
			allDepartments.add(
				new Department(rs.getInt("departmentID"), this)
			);
		}
		rs.close();
		stm.close();
	}
	
	public Department getDepartment(String departmentID) throws Exception
	{
		for (int i=0; i<allDepartments.size(); i++)
		{
			if (allDepartments.get(i).getField("departmentID").toString().equals(departmentID))
			{
				return allDepartments.get(i);
			}
		}
		throw new Exception("error: department not found ID:"+departmentID);
	}
	
	public ArrayList<Department> getAllDepartments()
	{
		// don't return a pointer to my own ArrayList
		return new ArrayList<Department> (allDepartments);
	}
	
	/**
	 * get Customer ID by email
	 * 
	 * @param email
	 */
	static public int getIdByMail(String email) throws EgoException, Exception
	{
		// sql query
		// search in employee
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
			"SELECT * FROM `employee` "
			+"WHERE account='"+ email +"';"
		);
		while (rs.next())
		{
			int id =  rs.getInt("customerID");
			rs.close();
			stm.close();
			return id;
		}
		// seach in customer
		rs = stm.executeQuery(
			"SELECT * FROM `customer` "
			+"WHERE systemAccount='"+ email +"';"
		);
		while (rs.next())
		{
			int id =  rs.getInt("customerID");
			rs.close();
			stm.close();
			return id;
		}

		// unknown employee
		throw new EgoException(EgoException.CUSTOMER_NOT_EXISTS);
	}
	
	/**
	 * get Employee Instance by mail
	 * 
	 * @return Employee
	 */
	public Employee getEmployeeByMail(String m) throws Exception
	{
		String e = " ";
		for (int i=0; i<allEmployees.size(); i++)
		{
			if (allEmployees.get(i).getField("account").toString().equals(m))
			{
				return allEmployees.get(i);
			}
			e += allEmployees.get(i).getField("account").toString()+",";
		}
		// employee does not exists
		throw new Exception("employee does not exists ("+ m +"). All Employees:" + e.substring(0, e.length()-1) );
	}
	
	/**
	 * get Employee Instance by ID
	 * 
	 * @return Employee
	 */
	public Employee getEmployee(String id) throws Exception
	{
		for (int i=0; i<allEmployees.size(); i++)
		{
			if (allEmployees.get(i).getField("employeeID").toString().equals(id))
			{
				return allEmployees.get(i);
			}
		}
		throw new Exception("error: employee not found ID:"+id);
	}
	
	public ArrayList<Employee> getAllEmployees()
	{
		// don't return a pointer to my own ArrayList
		return new ArrayList<Employee> (allEmployees);
	}
	
	/**
	 * returns all Employees without a assign to a department
	 * @return ArrayList<Employee>
	 */
	public ArrayList<Employee> getUnassignedEmployees()
	{
		ArrayList<Employee> ret = new ArrayList<Employee>();
		for (int i=0; i<allEmployees.size(); i++)
		{
			if (allEmployees.get(i).isUnassigned())
			{
				ret.add(allEmployees.get(i));
			}
		}
		
		return ret;
	}
	
	/**
	 * manually add a employee to this customer
	 * @param e Employee to add
	 */
	public void addEmployee(Employee e)
	{
		allEmployees.add(e);
	}
	
	/**
	 * create a new Customer DB entry
	 * 
	 * @param email systemAccount
	 * @param pwd password of systemAccount
	 *  - if password is 'null', a temporary system account will be mapped to this customer
	 *  
	 * @return instance of new customer
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	static public Customer newCustomer(String email, String pwd) throws Exception
	{
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		// get next customerID
		String nextID = "0";
		ResultSet rs = stm.executeQuery(
			"SELECT customerID FROM `customer` ORDER BY `customerID` DESC LIMIT 1;"
		);
		if (rs.next())
		{
			nextID = String.valueOf(rs.getInt(1) +1);
		}
		String temp = "";
		if (pwd == null)
		{
			// choose one temporary system account
			HashMap<String, String> accounts = (HashMap<String, String>) EgoSystem.config.get("temporarySystemAccounts");
			int n = Integer.parseInt(nextID) % accounts.size();
			int i=0;
			for (Map.Entry<String, String> e : accounts.entrySet())
			{
				if (i==n)
				{
					temp = e.getKey();
					break;
				}
				i++;
			}
			pwd = "";
		}
		Calendar cal = Calendar.getInstance();
		String cdate = String.valueOf(cal.get(Calendar.YEAR)) + "-"
			+ String.valueOf(cal.get(Calendar.MONTH) +1) +"-"
			+ String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
		
		stm.execute(
			"INSERT INTO `customer` ("
			+ "`customerID` ,"
			+ "`systemAccount` ,"
			+ "`password` ,"
			+ "`temporarySystemAccount` ,"
			+ "`createDate`"
			+") "
			+"VALUES ("
			+	"'"+ nextID +"','"+ email +"','"+ pwd +"', '"+ temp + "', '"+cdate+"'"
			+");"
		);
		return new Customer(Integer.parseInt( nextID ));
	}
}
